/* $Revision: $ $Date: $
 * 
 * Copyright (C) 2009 Robert Stephan
 * 
 * This file is part of *** CaCaO - Card Catalogs Online ***
 *
 * CaCaO is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * CaCao is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package cacao.util.sample;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;
import java.util.TreeSet;

import cacao.client.data.CaCaOHelper;
import cacao.server.CaCaOServer;


public class LoadSamples {
	public static final String FILE_EXTENSION = "jpg";
	public static final String SAMPLEFILES_PATH="e:\\temp\\ipac\\samples";
	public static final String FILESTORE="e:\\temp\\ipac\\filestore";

	//public static final String SAMPLEFILES_PATH="c:\\Temp\\ipac\\imagekatalog_sample";
	//public static final String FILESTORE="c:\\Temp\\ipac\\filestore";

	public static void main(String[] args){
		LoadSamples appl = new LoadSamples();
		appl.createCataloges();
		appl.createIndexes();
		appl.createItems();

		System.out.println("Sample data loaded!");
	}

	public void createCataloges(){
		Properties properties = new Properties();
		try {
			//Java6: properties.load(new BufferedReader(new InputStreamReader(this.getClass().getResourceAsStream("catalogs.properties"),"UTF-8")));
			properties.load(this.getClass().getResourceAsStream("catalogs.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}

		String[] keys =properties.keySet().toArray(new String[]{});
		Arrays.sort(keys);

		Properties ipacProps = CaCaOServer.getInstance().getProperties();
		try {
			Class.forName(ipacProps.getProperty("CaCaO.server.db.classname")).newInstance();
			Connection conn = DriverManager.getConnection(ipacProps.getProperty("CaCaO.server.db.connectionURL"));
			conn.setAutoCommit(false);
			Statement s = conn.createStatement();
			s.execute("DROP TABLE IF EXISTS catalogs");
			s.execute(ipacProps.getProperty("CaCaO.server.db.create.catalogs"));

			PreparedStatement ps  = conn.prepareStatement("INSERT INTO catalogs VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
			//
			for(String key:keys){
				String[]data = ((String)properties.get(key)).split("\\|");
				ps.clearParameters();	         
				// id column = INTEGER PK
				ps.setNull(1, Types.INTEGER); 
				// code = VARCHAR(8)"
				if(!data[0].equals("")){
					ps.setString(2, data[0]);	            
				}
				// name = VARCHAR(256)"
				if(!data[1].equals("")){
					ps.setString(3, data[1]);	            
				}
				//firstitemid = INTEGER
				if(!data[2].equals("")){
					ps.setInt(4, Integer.parseInt(data[2]));
				}
				//lastitemid = INTEGER
				if(!data[3].equals("")){
					ps.setInt(5, Integer.parseInt(data[3]));
				}
				//itemidlength = INTEGER
				if(!data[4].equals("")){
					ps.setInt(6, Integer.parseInt(data[4]));
				}
				//fileextension = VARCHAR(8)
				if(!data[5].equals("")){
					ps.setString(7, data[5]);
				}
				//orderenabled = BOOLEAN
				ps.setBoolean(8, "true".equals(data[6]));

				System.out.println(ps.toString());
				ps.executeUpdate();

			}
			ps.close();
			s.close();
			conn.commit();
			conn.close();
		} catch (SQLException ex) {
			// Fehler behandeln
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());

			ex.printStackTrace();

		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public void createItems(){
		Properties properties = new Properties();
		try {
			//Java6: properties.load(new BufferedReader(new InputStreamReader(this.getClass().getResourceAsStream("items.properties"),"UTF-8")));
			properties.load(this.getClass().getResourceAsStream("items.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}
		String[] keys =properties.keySet().toArray(new String[]{});
		Arrays.sort(keys);

		Properties ipacProps = CaCaOServer.getInstance().getProperties();
		try {
			Class.forName(ipacProps.getProperty("CaCaO.server.db.classname")).newInstance();
			Connection conn = DriverManager.getConnection(ipacProps.getProperty("CaCaO.server.db.connectionURL"));
			conn.setAutoCommit(false);
			Statement s = conn.createStatement();
			s.execute("DROP TABLE IF EXISTS items");
			s.execute(ipacProps.getProperty("CaCaO.server.db.create.items"));

			PreparedStatement ps  = conn.prepareStatement("INSERT INTO items VALUES (?, ?, ?)");
			for(String key:keys){
				String[]data = ((String)properties.get(key)).split("\\|");
				ps.clearParameters();	     
				ps.setString(1, "");
				ps.setString(2, "");
				ps.setString(3, "");
				// id = VARCHAR(32)
				if(!data[0].equals("")){
					ps.setString(1, data[0]);	            
				}
				// shelfmark = VARCHAR(64)
				if(!data[1].equals("")){
					ps.setString(2, data[1]);	            
				}
				// description = VARCHAR(2048)
				if(data.length>2 &&!data[2].equals("")){
					ps.setString(3, data[2]);
				}
				ps.executeUpdate();
			}
			ps.close();
			s.close();
			conn.commit();
			conn.close();
		} catch (SQLException ex) {
			// Fehler behandeln
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());

			ex.printStackTrace();

		} catch (Exception ex) {
			ex.printStackTrace();

		}
	}


	public void createIndexes(){
		List<String> names = new ArrayList<String>();
		Properties ipacProps = CaCaOServer.getInstance().getProperties();
		try {
			Class.forName(ipacProps.getProperty("CaCaO.server.db.classname")).newInstance();
			Connection conn = DriverManager.getConnection(ipacProps.getProperty("CaCaO.server.db.connectionURL"));
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			stmt.execute("DROP TABLE IF EXISTS catalogindex");
			stmt.execute(ipacProps.getProperty("CaCaO.server.db.create.catalogindex"));

			PreparedStatement pstmt  = conn.prepareStatement("INSERT INTO catalogindex VALUES (?, ?, ?, ?)");


			BufferedReader br = new BufferedReader(new InputStreamReader(this.getClass().getResourceAsStream("personennamen.csv"),"UTF-8"));
			while(br.ready()){
				String s = br.readLine();
				names.add(s.substring(1, s.length()-1));
			}
			Properties properties = new Properties();
			//Java6: properties.load(new BufferedReader(new InputStreamReader(this.getClass().getResourceAsStream("catalogs.properties"),"UTF-8")));
			properties.load(this.getClass().getResourceAsStream("catalogs.properties"));
			String[] keys =properties.keySet().toArray(new String[]{});
			Arrays.sort(keys);

			for(int j=0;j<keys.length;j++){
				String key=keys[j];
				TreeSet<String>usedNames = new TreeSet<String>();
				String[]data = ((String)properties.get(key)).split("\\|");
				String catalogid = data[0];
				int count = (Integer.parseInt(data[3]) / 100);
				//int firstitem = Integer.parseInt(data[2]);
				System.out.println(catalogid+"********************");
				for(int i=0;i<count;i++){
					String s = "x";
					while(s.substring(0,1).toLowerCase().equals(s.substring(0,1))){
						s = names.get((int)Math.round(Math.random()*names.size()));
					}
					usedNames.add(s);
				}
				int length = Integer.parseInt(data[4]);
				int c = 1;
				for(String s: usedNames){
					pstmt.clearParameters();

					String filename = CaCaOHelper.generateImageID(data[0], c, length);
					pstmt.setNull(1, Types.INTEGER);
					pstmt.setString(2, catalogid);
					pstmt.setString(3, s);
					pstmt.setString(4, filename);
					pstmt.executeUpdate();

					System.out.println(s+"\t:"+filename);			   		
					c = c+100+10-(int)Math.round(Math.random()*10);
					if(c>=count*100){
						break;
					}
				}
			}
			pstmt.close();
			stmt.close();
			conn.commit();
			conn.close();
		}
		catch(Exception e ){
			e.printStackTrace();
		}
	}

}